const router = require('koa-router')()
const path = require("path");
const fs = require("fs")
const pinyin = require('node-pinyin');

router.prefix('/user')

const avatarPath = path.join(path.resolve(__dirname, '../../../..'), '/public/images/avatars');
console.log(avatarPath, 'at student.js');

router.get("/getStudent", async (ctx, next) => {
  console.log(avatarPath, "输出路径");
  console.log(ctx.query);
  let query = ctx.query;
  let sql = "select student.user_id, avatar, student.user_name, student.student_name, student.signature, student.class_id, student.grade_id, student.institute_id, student.student_id, class.class_name, grade.grade_name, institute.institute_name from student join class on student.class_id = class.class_id join grade on student.grade_id = grade.grade_id join institute on student.institute_id = institute.institute_id  limit ? offset ?";
  let res = await ctx.db.EXCUTE(sql, [1 * query.pageSize, query.pageSize * (query.pageNum - 1)]);
  console.log(res);
  let total;
  let sql2 = "select count(*) as count from student"
  let res1 = await ctx.db.EXCUTE(sql2, []);
  total = res1[0].count;
  ctx.body = { code: 200, msg: "查询成功", data: { items: res, total } }
})

router.get("/getStudentOptions", async (ctx, next) => {
  let sql1 = `select * from institute 
              join grade on institute.institute_id = grade.institute_id
              join class on grade.grade_id = class.grade_id`;
  let sql2 = "select institute_id, institute_name from institute"
  let res1 = await ctx.db.EXCUTE(sql1, []);
  let res2 = await ctx.db.EXCUTE(sql2, []);
  // console.log(res1);
  // let obj = {};
  // for(let i in res2) {

  // }
  // let arr = [];

  function generateRelations(rows, index = 0, relations = []) {
    if (index === rows.length) {
      return relations;
    }

    const currentRow = rows[index];
    const instituteId = currentRow.institute_id;
    const gradeId = currentRow.grade_id;
    const classId = currentRow.class_id;

    let institute = relations.find((r) => r.value === instituteId);
    if (!institute) {
      institute = { value: instituteId, label: currentRow.institute_name, children: [] };
      relations.push(institute);
    }

    let grade = institute.children.find((g) => g.value === gradeId);
    if (!grade) {
      grade = { value: gradeId, label: currentRow.grade_name, number: currentRow.grade_num, children: [] };
      institute.children.push(grade);
    }

    let classObj = grade.children.find((c) => c.value === classId);
    if (!classObj) {
      classObj = { value: classId, label: currentRow.class_name, number: currentRow.class_number };
      grade.children.push(classObj);
    }

    return generateRelations(rows, index + 1, relations);
  }
  console.log(generateRelations(res1), "输出数据");
  let infoData = generateRelations(res1);
  console.log(infoData);
  ctx.body = { code: 200, msg: "查询成功", infoData }
})

router.post("/editStudent", async (ctx, next) => {
  const data = ctx.query;
  // console.log(ctx.request.body.get("avatar"), "avatar");
  // 在修改学生用户名的时候需要进行用户名的判定，相同则不允许更改
  let userNameSql = 'select user_name from user where user_name = ? and user_id != ?'
  let sql = "select avatar from student where user_id = ?"
  let sql3 = "update user set user_name = ? where user_id = ?";
  let sql2 = "update student set user_name = ?, signature = ?, class_id = ?, grade_id = ?, institute_id = ? where user_id = ?"
  let res4 = await ctx.db.EXCUTE(userNameSql, [data.user_name, data.user_id]);
  console.log(res4, "查询是否存在相同用户名");
  if (res4.length === 0) {
    // let res = await ctx.db.EXCUTE(sql, [data.user_id]);
    let res3 = await ctx.db.EXCUTE(sql3, [data.user_name, data.user_id]);
    let res2 = await ctx.db.EXCUTE(sql2, [data.user_name, data.signature, data.class_id, data.grade_id, data.institute_id, data.user_id]);
    ctx.upload.uploadAvatar(ctx, avatarPath, data.user_id, "student");
    ctx.body = { code: 200, msg: "修改成功" }
  } else {
    ctx.body = { code: 500, msg: "该用户名已存在" }
  }
})

router.post('/addStudent', async (ctx, next) => {
  try {
    const data = ctx.query;
    console.log(data);
    // 创建学生用户名为学生姓名拼音加时间戳的后6位，为了确保用户名的唯一性， 所以说添加学生的时候就不需要进行用户名查重判定了
    const user_name = pinyin(data.student_name, {
      style: 'normal',
    }).join('') + String(Date.now()).slice(-6);
    console.log("生成的学生的用户名");
    // 此处的创建学生ID传入的为1
    let id = ctx.snowFlake.createSnow(1);
    let usersql = 'insert into user (user_id, user_password, user_name, user_auth) values (?,?,?,?)';
    // 默认添加学生的密码是123456
    let user_password = ctx.Rsa.encrypt(ctx, "123456");
    console.log(user_password, "密码");
    console.log(ctx.Rsa.decrypt(ctx, user_password), "解密之后");
    let res1 = await ctx.db.EXCUTE(usersql, [id, user_password, user_name, "student_per"]);
    let sql = 'insert into student (user_id, avatar, user_name, signature, class_id, grade_id, institute_id, student_id, student_name) values (?,?,?,?,?,?,?,?,?)'
    let res = await ctx.db.EXCUTE(sql, [id, '', user_name, data.signature, data.class_id, data.grade_id, data.institute_id, data.student_id, data.student_name]);
    console.log(res);
    if (res.affectedRows != 0) {
      let sql = 'update class set class_number=class_number+1 where class_id=?'
      let res = await ctx.db.EXCUTE(sql, [data.class_id])
    }
    ctx.upload.uploadAvatar(ctx, avatarPath, id, 'student');
    console.log();
    ctx.body = { code: 200, msg: "修改成功" }
  } catch (err) {
    console.log(err, "上面的是报错信息");
    // ctx.body = {code: 500, msg: err}
    if (err.code == "ER_DUP_ENTRY") {
      ctx.body = { code: 500, msg: "插入数据已存在,请修改" }
    }
  }
})

router.post("/deleteStudent", async (ctx, next) => {
  let data = ctx.request.body;
  let imgSql = 'select avatar from student where user_id=?'
  let imgRes = await ctx.db.EXCUTE(imgSql, [data.user_id])
  console.log(imgRes);
  let sql = 'delete from user where user_id = ?'
  let sql1 = 'delete from student where user_id = ?'
  let res = ctx.db.EXCUTE(sql, [data.user_id]);
  let res1 = ctx.db.EXCUTE(sql1, [data.user_id]);

  console.log(res);

  if (res.affectedRows != 0) {
    let sql = 'update class set class_number=class_number-1 where class_id=?'
    let res = await ctx.db.EXCUTE(sql, [data.class_id])
  }

  if (res.affectedRows != 0 && imgRes[0].avatar) {
    fs.stat(path.join(avatarPath, imgRes[0].avatar), (err, state) => {
      if (err) {
        console.log(err);
        throw new Error(err.message);
      } else {
        fs.unlinkSync(path.join(avatarPath, imgRes[0].avatar));
      }
    })
  }

  ctx.body = { code: 200, msg: "删除成功" }
})



module.exports = router
